package org.lq.student.dao.impl;



import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.lq.student.dao.StudentInfoDao;
import org.lq.student.entity.StudentInfo;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.List;


/**
 * @author 秦洪涛
 * @create 2020-10-13-18:29
 */

public class StudentInfoDaoImpl implements StudentInfoDao {
    private static final Logger log=Logger.getLogger(StudentInfoDaoImpl.class);

    @Override
    public StudentInfo getStudentByName(String name) {
        log.info("根据姓名获取学生成功");
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select  * from v_student_info where studentName like ?",
                    new BeanHandler<StudentInfo>(StudentInfo.class),"%"+name+"%");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            log.error("根据姓名获取学生错误");
        }

        return null;
    }

    @Override
    public int getCount(String value) {
        log.info("根据姓名获取总行数成功");
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select count(1) from v_student_info where studentName like ?",
                    new ScalarHandler<Long>(),
                    "%"+value+"%").intValue();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            log.error("根据姓名获取总行数错误");
        }

        return 0;
    }

    @Override
    public List<StudentInfo> pageByValues(int startIndex, int pageSize, String value) {
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select * from v_student_info where studentName like ? limit ?,?",
                    new BeanListHandler<StudentInfo>(StudentInfo.class),
                    "%"+value+"%",startIndex,pageSize);
        } catch (SQLException throwables) {
            log.error("根据姓名分页错误");
        }
        log.info("根据姓名分页成功");

        return null;
    }

    @Override
    public int save(StudentInfo studentInfo) {
        log.info("添加学生成功");
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).update("insert into student_info ( staff_id" +
                    ",class_id" +
                    ",student_name" +
                    ",student_sex" +
                    ",student_age" +
                    ",student_tellphone" +
                    ",student_email" +
                    ",student_idcard" +
                    ",student_address" +
                    ",student_birthday" +
                    ",student_school" +
                    ",student_qq" +
                    ",student_parents_name" +
                    ",student_parents_phone" +
                    ",student_pro" +
                    ",student_pro_city" +
                    ",student_type" +
                    ",student_ispay" +
                    ",student_sate" +
                    ",student_mark" +
                    ",student_desc" +
                    ",student_number" +
                    ",student_password) " +
                    "values (?,?,?,?,?,?,?,?," +
                            "?,?,?,?,?,?,?,?," +
                            "?,?,?,?,?,?,?)",studentInfo.getStaffId(),
                    studentInfo.getClassId(),
                    studentInfo.getStudentName(),
                    studentInfo.getStudentSex(),
                    studentInfo.getStudentAge(),
                    studentInfo.getStudentTellphone(),
                    studentInfo.getStudentEmail(),
                    studentInfo.getStudentIdcard(),
                    studentInfo.getStudentAddress(),
                    studentInfo.getStudentBirthday(),
                    studentInfo.getStudentSchool(),
                    studentInfo.getStudentQQ(),
                    studentInfo.getStudentParentsName(),
                    studentInfo.getStudentParentsPhone(),
                    studentInfo.getStudentPro(),
                    studentInfo.getStudentProCity(),
                    studentInfo.getStudentType(),
                    studentInfo.getStudentIspay(),
                    studentInfo.getStudentSate(),
                    studentInfo.getStudentMark(),
                    studentInfo.getStudentDesc(),
                    studentInfo.getStudentNumber(),
                    studentInfo.getStudentPassword());
        } catch (SQLException throwables) {
            log.error("添加学生错误");
        }

        return 0;
    }

    @Override
    public int update(StudentInfo studentInfo) {
        log.info("学员信息修改成功");
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).update("update student_info set staff_id=?" +
                            ",class_id=?" +
                            ",student_name=?" +
                            ",student_sex=?" +
                            ",student_age=?" +
                            ",student_tellphone=?" +
                            ",student_email=?" +
                            ",student_idcard=?" +
                            ",student_address=?" +
                            ",student_birthday=?" +
                            ",student_school=?" +
                            ",student_qq=?" +
                            ",student_parents_name=?" +
                            ",student_parents_phone=?" +
                            ",student_pro=?" +
                            ",student_pro_city=?" +
                            ",student_type=?" +
                            ",student_ispay=?" +
                            ",student_sate=?" +
                            ",student_mark=?" +
                            ",student_desc=?" +
                            ",student_number=?" +
                            ",student_password=? where student_id=? "
                    ,studentInfo.getStaffId(),
                    studentInfo.getClassId(),
                    studentInfo.getStudentName(),
                    studentInfo.getStudentSex(),
                    studentInfo.getStudentAge(),
                    studentInfo.getStudentTellphone(),
                    studentInfo.getStudentEmail(),
                    studentInfo.getStudentIdcard(),
                    studentInfo.getStudentAddress(),
                    studentInfo.getStudentBirthday(),
                    studentInfo.getStudentSchool(),
                    studentInfo.getStudentQQ(),
                    studentInfo.getStudentParentsName(),
                    studentInfo.getStudentParentsPhone(),
                    studentInfo.getStudentPro(),
                    studentInfo.getStudentProCity(),
                    studentInfo.getStudentType(),
                    studentInfo.getStudentIspay(),
                    studentInfo.getStudentSate(),
                    studentInfo.getStudentMark(),
                    studentInfo.getStudentDesc(),
                    studentInfo.getStudentNumber(),
                    studentInfo.getStudentPassword(),
                    studentInfo.getStudentId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        log.error("学员信息修改失败");
        }
        return 0;
    }

    @Override
    public int delete(int id) {
        log.info("学员信息删除成功");
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).update("delete from student_info where student_id=?",id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            log.error("学员信息删除失败");
        }
        return 0;
    }

    @Override
    public StudentInfo getById(int id) {
        log.info("根据id查询学员成功");
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select  * from v_student_info where studentId=?",
                    new BeanHandler<StudentInfo>(StudentInfo.class),id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            log.error("根据id查询学员失败");
        }
        return null;
    }

    @Override
    public int getCount() {
        log.info("获取学员个数（总行数）成功");
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select count(1) from v_student_info",
                    new ScalarHandler<Long>()).intValue();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            log.error("获取学员个数（总行数）失败");
        }
        return 0;
    }

    @Override
    public List<StudentInfo> pageList(int startIndex, int pageSize) {
        log.info("起始下标:"+startIndex+"每页显示行数:"+pageSize+";分页显示成功");
        try {
            return new QueryRunner(JDBCUtil.getDataSource()).query("select * from v_student_info limit ?,?",
                    new BeanListHandler<StudentInfo>(StudentInfo.class),startIndex,pageSize);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            log.error("起始下标:"+startIndex+"每页显示行数:"+pageSize+";分页显示失败");
        }
        return null;
    }

//    @Override
//    public int getCount(String... values) {
//
////        try {
////            return new QueryRunner(JDBCUtil.getDataSource()).query("select count(1) from v_student_info where studentName like ?",
////                    new ScalarHandler<Long>(),
////                    "%"+values+"%").intValue();
////        } catch (SQLException throwables) {
////            throwables.printStackTrace();
////        }
//
//        return 0;
//    }
//
//    @Override
//    public List<StudentInfo> pageByValues(int startIndex, int pageSize, String... value) {
////        try {
////            return new QueryRunner(JDBCUtil.getDataSource()).query("select * from v_student_info where studentName like ? limit ?,?",
////                    new BeanListHandler<StudentInfo>(StudentInfo.class),
////                    "%"+value+"%",startIndex,pageSize);
////        } catch (SQLException throwables) {
////            throwables.printStackTrace();
////        }
//        return null;
//    }
}
